package dao.impl;

import dao.OrderDao;
import pojo.Goods;
import pojo.Order;
import pojo.User;
import util.BaseDao;
import util.PageBean;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * 用户订单类Dao接口类实现
 */
public class OrderDaoImpl implements OrderDao {
    BaseDao bd = new BaseDao();
    /**
     * 订单分页查询
     * @param pb 分页查询工具
     * @param ename 根据用户账号查询
     * @param state 根据订单状态查询
     */
    @Override
    public PageBean orderPageList(PageBean pb, String ename, int state) {
        List<Order> list = new ArrayList<Order>();
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = bd.getConnection();
            String s = "";
            if (null != ename && !ename.equals("")) {
                s += "and  u_account LIKE '%" + ename + "%'";
            }
            switch (state){
                case 0:  s += "and orders_state = 0";break;
                case 1:  s += "and orders_state = 1";break;
                case 2:  s += "and orders_state = 2";break;
                case 3:  s += "and orders_state = 3";break;
                case 4:  s += "and orders_state = 4";break;
                default:  s += "";break;
            }
            String sql = " SELECT * FROM (" +
                    " SELECT ROWNUM r,a.* FROM (select od.*,eu.u_account from orders od,e_user eu where od.consumer_id=eu.u_id)a  " +
                    "  WHERE 1=1 " + s + " and ROWNUM<=?" +
                    " )WHERE r>?";
            ps = conn.prepareStatement(sql);
            ps.setInt(1, pb.getPageIndex() * pb.getPageSize());
            ps.setInt(2, pb.getPageSize() * (pb.getPageIndex() - 1));
            rs = ps.executeQuery();
            while (rs.next()) {
                Order order = new Order();
                order.setOrderId(rs.getInt("ORDERS_ID"));
                order.setConsumerId(rs.getInt("CONSUMER_ID"));
                order.setConsumerAccount(rs.getString("u_account"));
                order.setWareId(rs.getString("WARE_ID"));
                order.setOrderPrice(rs.getInt("ORDERS_PRICE"));
                order.setOrderState(rs.getInt("ORDERS_STATE"));
                order.setOrderAddress(rs.getString("ORDERS_ADDRESS"));
                order.setOrderRemarks(rs.getString("ORDERS_REMARKS"));
                order.setOrderDate(rs.getTimestamp("ORDERS_DATE"));
                list.add(order);
            }
            pb.setList(list);
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            bd.release(rs,ps,conn);
        }
        return pb;
    }
    /**
     * 商品分页查询
     * @param pb 分页查询工具
     */
    @Override
    public PageBean goodsPageList(PageBean pb) {
        List<Goods> list = new ArrayList<Goods>();
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = bd.getConnection();
            String sql = " SELECT * FROM (" +
                    " SELECT ROWNUM r,g.* FROM  goods g" +
                    "  WHERE 1=1  and ROWNUM<=?" +
                    " )WHERE r>?";
            ps = conn.prepareStatement(sql);
            ps.setInt(1, pb.getPageIndex() * pb.getPageSize());
            ps.setInt(2, pb.getPageSize() * (pb.getPageIndex() - 1));
            rs = ps.executeQuery();
            while (rs.next()) {
                Goods goods=new Goods();
                goods.setGoId(rs.getInt("go_id"));
                goods.setGoName(rs.getString("go_name"));
                goods.setGoSalesVolume(rs.getInt("go_salesvolume"));
                goods.setGoPraise(rs.getInt("go_praise"));
                goods.setGoUptime(rs.getTimestamp("go_uptime"));
                goods.setGoPictureUrl(rs.getString("go_pictureurl"));
                goods.setGoDescribe(rs.getString("go_describe"));
                goods.setGoCost(rs.getInt("go_cost"));
                goods.setGoPrice(rs.getInt("go_price"));
                goods.setGoStock(rs.getInt("go_stock"));
                goods.setGoState(rs.getInt("go_state"));
                goods.setClId(rs.getInt("cl_id"));
                goods.setGoSecKill(rs.getInt("go_seckill"));
                list.add(goods);
            }
            pb.setList(list);
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            bd.release(rs,ps,conn);
        }
        return pb;
    }
    /**
     * 查询所有订单
     * @return list集合
     */
    @Override
    public List<Order> list() {
        List<Order> list = new ArrayList<Order>();
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = bd.getConnection();
            String sql = "select * from orders";
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();
            while (rs.next()) {
                Order order = new Order();
                order.setOrderId(rs.getInt("ORDERS_ID"));
                order.setConsumerId(rs.getInt("CONSUMER_ID"));
                order.setWareId(rs.getString("WARE_ID"));
                order.setOrderPrice(rs.getInt("ORDERS_PRICE"));
                order.setOrderState(rs.getInt("ORDERS_STATE"));
                order.setOrderAddress(rs.getString("ORDERS_ADDRESS"));
                order.setOrderRemarks(rs.getString("ORDERS_REMARKS"));
                order.setOrderDate(rs.getTimestamp("ORDERS_DATE"));
                list.add(order);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            bd.release(rs,ps,conn);
        }
        return list;
    }

    /**
     * 根据id查询用户订单
     * @param id 订单id
     * @return 返回结果
     */
    @Override
    public Order finaOrder(int id) {
        Order order = new Order();
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = bd.getConnection();
            String sql = "select * from orders where orders_id=? ";
            ps = conn.prepareStatement(sql);
            ps.setInt(1, id);
            rs = ps.executeQuery();
            while (rs.next()) {
                order.setOrderId(rs.getInt("ORDERS_ID"));
                order.setConsumerId(rs.getInt("CONSUMER_ID"));
                order.setWareId(rs.getString("WARE_ID"));
                order.setOrderPrice(rs.getInt("ORDERS_PRICE"));
                order.setOrderState(rs.getInt("ORDERS_STATE"));
                order.setOrderAddress(rs.getString("ORDERS_ADDRESS"));
                order.setOrderRemarks(rs.getString("ORDERS_REMARKS"));
                order.setOrderDate(rs.getTimestamp("ORDERS_DATE"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            bd.release(rs,ps,conn);
        }
        return order;
    }
    /**
     * 查询用户已完成的订单
     * @return 返回结果
     */
    @Override
    public List<Order> orderList() {
        List<Order> list = new ArrayList<Order>();
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = bd.getConnection();
            String sql = "select * from orders where ORDERS_STATE=0 ";
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();
            while (rs.next()) {
                Order order = new Order();
                order.setOrderId(rs.getInt("ORDERS_ID"));
                order.setConsumerId(rs.getInt("CONSUMER_ID"));
                order.setWareId(rs.getString("WARE_ID"));
                order.setOrderPrice(rs.getInt("ORDERS_PRICE"));
                order.setOrderState(rs.getInt("ORDERS_STATE"));
                order.setOrderAddress(rs.getString("ORDERS_ADDRESS"));
                order.setOrderRemarks(rs.getString("ORDERS_REMARKS"));
                order.setOrderDate(rs.getTimestamp("ORDERS_DATE"));
                list.add(order);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            bd.release(rs,ps,conn);
        }
        return list;
    }
    /**
     * 根据用户id查询用户已完成的订单
     * @param id  用户id
     * @return 返回结果
     */
    @Override
    public Order finalOrder(int id) {
        Order order = new Order();
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = bd.getConnection();
            String sql = "select * from orders where ORDERS_STATE=0 and CONSUMER_ID=? ";
            ps = conn.prepareStatement(sql);
            ps.setInt(1, id);
            rs = ps.executeQuery();
            while (rs.next()) {
                order.setOrderId(rs.getInt("ORDERS_ID"));
                order.setConsumerId(rs.getInt("CONSUMER_ID"));
                order.setWareId(rs.getString("WARE_ID"));
                order.setOrderPrice(rs.getInt("ORDERS_PRICE"));
                order.setOrderState(rs.getInt("ORDERS_STATE"));
                order.setOrderAddress(rs.getString("ORDERS_ADDRESS"));
                order.setOrderRemarks(rs.getString("ORDERS_REMARKS"));
                order.setOrderDate(rs.getTimestamp("ORDERS_DATE"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            bd.release(rs,ps,conn);
        }
        return order;
    }
    /**
     * 新增订单
     * @param order 订单对象
     * @return 返回结果
     */
    @Override
    public int InseOrder(Order order) {
        int bo = 0;//返回影响行数
        String sql = "insert into orders values(sequence_orders.nextval,?,?,?,?,?,?,?)";
        bo = bd.cud(sql, order.getConsumerId(),order.getWareId(),order.getOrderPrice(),order.getOrderState(),order.getOrderAddress(),order.getOrderRemarks(),order.getOrderDate());
        return bo;
    }

    /**
     * 修改订单
     * @param order 订单对象
     * @return 返回结果
     */
    @Override
    public int updaOrder(Order order) {
        int bo = 0;//返回影响行数
        String sql = "update orders set CONSUMER_ID=? ,WARE_ID=? ,ORDERS_PRICE=? ,ORDERS_STATE=?,ORDERS_ADDRESS=?,ORDERS_REMARKS=?,ORDERS_DATE=? where orders_id=?";
        bo = bd.cud(sql,order.getConsumerId(),order.getWareId(),order.getOrderPrice(),order.getOrderState(),order.getOrderAddress(),order.getOrderRemarks(),order.getOrderDate(),order.getOrderId());
        return bo;
    }
    /**
     * 删除订单
     * @param id 订单id
     * @return 返回结果
     */
    @Override
    public int deleOrder(int id) {
        int bo = 0;
        String sql = "delete from orders where orders_id=?";
        bo = bd.cud(sql,id);
        return bo;
    }

    /**
     * 根据组合查询统计订单总页数
     * @param ename 用户账号
     * @param state 订单状态
     * @return 返回结果
     */
    @Override
    public int orderCount(String ename, int state) {
        int count = 0;
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = bd.getConnection();
            String s = "";
            if (null != ename && !ename.equals("")) {
                s += "  and  u_account LIKE '%" + ename + "%' ";
            }
            switch (state){
                case 0:  s += "and ORDERS_STATE = 0";break;
                case 1:  s += "and ORDERS_STATE = 1";break;
                case 2:  s += "and ORDERS_STATE = 2";break;
                case 3:  s += "and ORDERS_STATE = 3";break;
                case 4:  s += "and ORDERS_STATE = 4";break;
                default:  s += "";break;
            }
            String sql = "SELECT count(*) FROM orders od inner join e_user eu on  eu.u_id=od.consumer_id  where 1=1 " + s;
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();
            while (rs.next()) {
                count = rs.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return count;
    }

    /**
     *  根据用户id查询用户信息
     * @param id 用户id
     */
    @Override
    public User finaUser(int id) {
        User user = new User();
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = bd.getConnection();
            String sql = "select * from e_user where u_id=? ";
            ps = conn.prepareStatement(sql);
            ps.setInt(1, id);
            rs = ps.executeQuery();
            while (rs.next()) {
                user.setuID(rs.getInt("u_id"));
                user.setuAccount(rs.getString("u_account"));
                user.setuPassword(rs.getString("u_password"));
                user.setuNickName(rs.getString("u_nickname"));
                user.setuGender(rs.getInt("u_gender"));
                user.setuPhoneNumber(rs.getString("u_phonenumber"));
                user.setuEmail(rs.getString("u_email"));
                user.setuAddress(rs.getString("u_address"));
                user.setuIntegral(rs.getInt("u_integral"));
                user.setuRegdate(rs.getTimestamp("u_regdate"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            bd.release(rs,ps,conn);
        }
        return user;
    }

    /**
     * 查询所有用户
     * @return list集合
     */
    @Override
    public List<User> userList() {
        List<User> list = new ArrayList<User>();
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = bd.getConnection();
            String sql = "select * from e_user";
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();
            while (rs.next()) {
                User user = new User();
                user.setuID(rs.getInt("u_id"));
                user.setuAccount(rs.getString("u_account"));
                user.setuPassword(rs.getString("u_password"));
                user.setuNickName(rs.getString("u_nickname"));
                user.setuGender(rs.getInt("u_gender"));
                user.setuPhoneNumber(rs.getString("u_phonenumber"));
                user.setuEmail(rs.getString("u_email"));
                user.setuAddress(rs.getString("u_address"));
                user.setuIntegral(rs.getInt("u_integral"));
                user.setuRegdate(rs.getTimestamp("u_regdate"));
                list.add(user);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            bd.release(rs,ps,conn);
        }
        return list;
    }

    /**
     * 根据商品id查询商品信息
     * @param id 商品id
     */
    @Override
    public Goods finaGoods(int id) {
        Goods goods = new Goods();
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = bd.getConnection();
            String sql = "select * from goods where go_id=? ";
            ps = conn.prepareStatement(sql);
            ps.setInt(1, id);
            rs = ps.executeQuery();
            while (rs.next()) {
                goods.setGoId(rs.getInt("go_id"));
                goods.setGoName(rs.getString("go_name"));
                goods.setGoSalesVolume(rs.getInt("go_salesvolume"));
                goods.setGoPraise(rs.getInt("go_praise"));
                goods.setGoUptime(rs.getTimestamp("go_uptime"));
                goods.setGoPictureUrl(rs.getString("go_pictureurl"));
                goods.setGoDescribe(rs.getString("go_describe"));
                goods.setGoCost(rs.getInt("go_cost"));
                goods.setGoPrice(rs.getInt("go_price"));
                goods.setGoStock(rs.getInt("go_stock"));
                goods.setGoState(rs.getInt("go_state"));
                goods.setClId(rs.getInt("cl_id"));
                goods.setGoSecKill(rs.getInt("go_seckill"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            bd.release(rs,ps,conn);
        }
        return goods;
    }

    /**
     * 查询所有商品
     * @return list集合
     */
    @Override
    public List<Goods> goodsList() {
        List<Goods> list = new ArrayList<Goods>();
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = bd.getConnection();
            String sql = "select * from goods";
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();
            while (rs.next()) {
                Goods goods = new Goods();
                goods.setGoId(rs.getInt("go_id"));
                goods.setGoName(rs.getString("go_name"));
                goods.setGoSalesVolume(rs.getInt("go_salesvolume"));
                goods.setGoPraise(rs.getInt("go_praise"));
                goods.setGoUptime(rs.getTimestamp("go_uptime"));
                goods.setGoPictureUrl(rs.getString("go_pictureurl"));
                goods.setGoDescribe(rs.getString("go_describe"));
                goods.setGoCost(rs.getInt("go_cost"));
                goods.setGoPrice(rs.getInt("go_price"));
                goods.setGoStock(rs.getInt("go_stock"));
                goods.setGoState(rs.getInt("go_state"));
                goods.setClId(rs.getInt("cl_id"));
                goods.setGoSecKill(rs.getInt("go_seckill"));
                list.add(goods);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            bd.release(rs,ps,conn);
        }
        return list;
    }

    /**
     * 根据用户账号查询用户对象
     * @param account 用户账号
     * @return
     */
    @Override
    public User userName(String account) {
        User user = new User();
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = bd.getConnection();
            String sql = "select * from e_user where u_account=? ";
            ps = conn.prepareStatement(sql);
            ps.setString(1, account);
            rs = ps.executeQuery();
            while (rs.next()) {
                user.setuID(rs.getInt("u_id"));
                user.setuPassword(rs.getString("u_password"));
                user.setuNickName(rs.getString("u_nickname"));
                user.setuGender(rs.getInt("u_gender"));
                user.setuPhoneNumber(rs.getString("u_phonenumber"));
                user.setuEmail(rs.getString("u_email"));
                user.setuAddress(rs.getString("u_address"));
                user.setuIntegral(rs.getInt("u_integral"));
                user.setuRegdate(rs.getTimestamp("u_regdate"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            bd.release(rs,ps,conn);
        }
        return user;
    }
}
